Release 10.1A: OpenEdge Development:
Basic Database Tools

Index Editor option

Use the Schema Index Editor menu option to define, rename, or delete indexes for the selected table. An index is a field or combination of fields used to rapidly retrieve a particular record in a table. A single index can be made up of multiple fields. For example, if you know you are always going to access the order-line table by using a combination of the Order-num and Line-num fields, you might want to create an index with those two fields as its components.

Note: The current table must contain fields before you can define an index.

When you choose this option, the Data Dictionary alphabetically lists all the tables defined for your database. When you select a table, a window similar to the one shown in Figure 9–14 appears.

Figure 9–14: Index Editor window

Use the Index Editor to display the existing indexes and their information for the selected table.

The Index Editor window contains the following fields:

Name

Specifies the index name. It does not have to be the same name as the field name. Index names can be up to 32 character long and can consist of alphabetic characters, digits, and the characters $, &, #, %, –, and _. In addition, index names must begin with a letter (A–Z or a–z). You cannot use reserved Progress 4GL keywords as index names. Index names are not case sensitive.

Unique/Non-Unique

Defines whether every entry in the index must be different. Enter yes if only one record in a table has a particular index value. For example, the customer table cannot contain two records with the same customer number, so customer number can be a unique index. Conversely, since many customers might be in the same sales region, sales-region should not be a unique index.

ACTIVE

Defines whether the index is initially active, that is, whether it updates itself every time a new record is created, deleted, or modified. If you are defining an index in a very large table, it is more efficient to define the index as initially inactive. (When an index is inactive, you cannot use it to retrieve or order records.) You can subsequently activate the index by running the PROUTIL utility with the IDXBUILD qualifier. This is much faster than building the index from within the Data Dictionary when the database contains a large amount of data. For more information about PROUTIL, see OpenEdge Data Management: Database Administration .

ROWID

For non-OpenEdge databases only. See the applicable OpenEdge DataServer Guide for more information on the ROWID property.

Word

Defines whether you can search on any word within a field. A word index contains all the words from a text field or array of text fields; thus, you can search for records that contain specific words or phrases. All words in the field are index entries. For more information about word indexes, see the chapter on database access in OpenEdge Development: Progress 4GL Handbook .

Seq

Defines the order in which fields form the index. For example, cust-order is a compound index, formed by the Cust-num and the Order-num fields. The Cust-num field has a sequence number of 1, and the Order-num field has a sequence number of 2.

Field Name

Displays the field name.

Type

Specifies the field data type. See the "Field Editor option" section section in this chapter for more information about data types.

Note: BLOB and CLOB data types cannot participate in indexes.
Asc/Dsc

Specifies how the index sorts records. Value is either asc for ascending or dsc for descending.

Abbr

Specifies that you can search an index using the first few characters of a field without using the BEGINS phrase, if the field is a character data field. Indexes not comprised of character data require an exact match. If the index has more than one field, this setting applies only to the last field.

Besides the fields described above, the Index Editor also lists options at the bottom of its display. Table 9–18 describes these options.

Table 9–18: Index Editor options

Option	Description
Next	Displays the next index in the table.
Prev	Displays the previous index in the table.
First	Displays the first index in the table.
Last	Displays the last index in the table.
Rename	Renames the selected index. The name you enter cannot already be used to name an index in the current database.
Add	Adds a new index to the table.
Delete	Deletes the selected index. You cannot delete a primary index without making another index the primary index.
ChangePrimary	Changes the primary index for the table.
MakeInactive	Makes an active index inactive. (When an index is inactive, you cannot use it to retrieve or order records.) To reactivate an index, you must run PROUTIL with the IDXBUILD qualifier.
Browse	Browses through the entire index component list. This is especially helpful when the index is made up of multiple fields.
SwitchTable	Shows the list of tables in the database, then switches to another table to edit its indexes.
GoField	Accesses the Field Editor.
Undo	Undoes changes you made to the index attributes during this session.
Exit	Saves your changes, exits the Index Editor, and returns to the Data Dictionary Main Menu.

Choose the field you want to edit, then choose a menu option. When you choose Add from the Index Editor, the Index Editor prompts you for the name of the new index. When you enter data in the Name, Unique, Active, and Word fields, the Index Add Option dialog box, shown in Figure 9–15, appears.

Figure 9–15: Index Add Option dialog box

The Index Editor lists the available fields and their data types. When you choose a field, the Index Editor prompts you to specify whether you want the index component to be ascending or descending. After you specify the sort order, the Index Editor lets you choose more fields. You can specify up to 16 fields to define an index.

The Index Editor lists the selected fields above the divider line on the window. Select all fields, then press GO. The Index Editor prompts you to specify whether you want to use the Abbreviate option for character fields then returns you to the main Index Editor window and adds the new index to the list.